package com.training.api.repository;

import com.training.bean.Course;
import com.training.bean.LearningTrackData;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface CourseRepository extends JpaRepository<Course, Integer> {
    @Query("select data from Course data where data.csid=:csid")
    List<Course> findCourseByCSId(int csid);
    
    // 三表联查获取指定阶段下所有课程及用户收藏和课程内容所有的总时长
    @Query("SELECT new Course(c, col.cid, SUM(cc.duration)) FROM Course c " +
            "LEFT JOIN Collect col ON c.id = col.cid AND col.uid = :uid " +
            "LEFT JOIN CourseContent cc ON c.id = cc.cid " +
            "WHERE c.csid = :csid " +
            "GROUP BY c.id, col.cid")
    List<Course> findCoursesWithCollectAndProgressByCSId(@Param("csid") int csid, @Param("uid") int uid);
}